from datetime import datetime
from init_db import get_db_connection
from get_weather import get_weather
from get_price import price

def handle_none_values(data):
    # 将 None 值转换为适当的默认值，例如空字符串
    return tuple("" if x is None else x for x in data)

def write_weather_to_database():
    conn, cursor = get_db_connection()  # 获取连接和游标
    try:
        # 执行 get_weather 函数获取天气信息
        response_data = get_weather()
        print("获取的天气数据:", response_data)
        # 检查 response_data 结构
        if not isinstance(response_data, dict) or 'data' not in response_data:
            raise ValueError("response_data 不包含有效的天气数据")

        weather_data_list = response_data['data']
        if not weather_data_list:
            raise ValueError("data 列表为空")

        # 获取第一条天气数据
        weather_data = weather_data_list[0]
        print("处理的天气数据:", weather_data)

        #表名为 weather，包含相应的列
        sql = """
        INSERT INTO weather (
            longitude, latitude, dateTime, 
            weatherType, visibility, temperature, 
            temperatureMax, temperatureMin, rainHourly, 
            rainDay, windSpeedAvg, windSpeedMax, 
            relativeHumidity, 
            snowfall, snow, earlyWarn, alert
        ) 
        VALUES (%s, %s, %s, %s, %s, CAST(%s AS DECIMAL), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        # 处理 None 值
        values = (
            weather_data['longitude'],
            weather_data['latitude'],
            weather_data['dateTime'],  # 已经是字符串格式，无需转换
            weather_data['weatherType'],
            weather_data['visibility'],
            weather_data['temperature'],
            weather_data['temperatureMax'],
            weather_data['temperatureMin'],
            weather_data['rainHourly'],
            weather_data['rainDay'],
            weather_data['windSpeedAvg'],
            weather_data['windSpeedMax'],
            weather_data['relativeHumidity'],
            weather_data['snowfall'],  # 允许 NULL
            weather_data['snow'],  # 允许 NULL
            weather_data['earlyWarn'],  # 允许 NULL
            weather_data['alert']  # 允许 NULL
        )

        print("插入数据库的值:", values)
        cursor.execute(sql, values)
        print("天气信息写入成功")
        conn.commit()
    except Exception as e:
        print(f"写入数据库时发生错误：{e}")
        if conn:
            conn.rollback()  # 出错时回滚事务
    finally:
        # 关闭游标和数据库连接
        if cursor:
            cursor.close()
        if conn:
            conn.close()

def write_price_to_database():
    conn, cursor = get_db_connection()  # 获取连接和游标
    try:
        # 获取当前时间
        now_date = datetime.now().date()
        print(now_date)

        # 执行 price 函数获取价格信息
        result = price()
        for product_info in result:
            goods, notes, price_val, unit = product_info  # 注意避免与变量名重复
            print(f"{goods}（{notes}）：{price_val}元/{unit}")

        # 批量插入数据
        sql = "INSERT INTO market_prices (query_time, goods, notes, price, unit) VALUES (%s, %s, %s, %s, %s)"
        values = [(now_date, *product_info) for product_info in result]
        cursor.executemany(sql, values)
        print("写入成功")
        # 提交事务
        conn.commit()
    except Exception as e:
        print(f"写入数据库时发生错误：{e}")
        if conn:
            conn.rollback()  # 出错时回滚事务
    finally:
        # 关闭游标和数据库连接
        if cursor:
            cursor.close()
        if conn:
            conn.close()

def execute_query(query, params=None):
    conn, cursor = get_db_connection()
    try:
        if params:
            cursor.execute(query, params)
        else:
            cursor.execute(query)
        conn.commit()
    except Exception as e:
        print(f"出现报错: {e}")
    finally:
        cursor.close()
        conn.close()

def fetch_data(query, params=None):
    conn, cursor = get_db_connection()  # 获取连接和游标
    try:
        if params:
            cursor.execute(query, params)
        else:
            cursor.execute(query)
        data = cursor.fetchall()
    finally:
        cursor.close()
        conn.close()
    return data



#添加一个命令行参数检查，以决定要运行哪个函数。这样可通过传递不同的参数来控制运行的函数。
if __name__ == '__main__':
    import sys
    if len(sys.argv) > 1:
        #len(sys.argv) > 1这个条件检查 sys.argv 列表的长度是否大于1。也就是是否有传递额外的参数。如果没有额外参数，sys.argv 的长度为1（只包含脚本名）
        if sys.argv[1] == 'weather':
            write_weather_to_database()
        elif sys.argv[1] == 'price':
            write_price_to_database()




